/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao


import entity.ServiceRequest;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author JAVIER
 */
public class ServiceRequestDAO implements DAO <ServiceRequest> {

    private Integer ID = 0; 
    
    private static final String SQL_INSERT = "INSERT INTO ServiceRequest ( clientid, employeeid, registrationDate, origin, totalSamples, shipmentRecord, billingPending, billNumber, serviceOrder, billingDate) VALUES ( ?, ?, ?,?, ?, ?, ?, ?,?, ?)";
    
    private static final String SQL_SELECT = " SELECT * FROM ServiceRequest where id= ?";
    
    private static final String SQL_SELECT_PROPERTY = " SELECT * FROM ServiceRequest where ? = ?" ;
    
    private static final String SQL_SELECT_All = "SELECT * FROM ServiceRequest";
    
    private static final String SQL_UPDATE = " UPDATE ServiceRequest SET id = ?, clientid = ?, employeeid = ?, registrationDate = ?, origin = ?, totalSamples = ?, shipmentRecord = ?, billingPending = ?, billNumber = ?, serviceOrder = ?, billingDate = ? WHERE id = ? ";

    private static final String SQL_DELETE = " DELETE FROM ServiceRequest WHERE id = ?";
    
    Integer create(ServiceRequest Serv, Connection connect) 
        throws SQLException {

        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
            
            ps.setInt(1, Serv.getClient().getId());
            ps.setInt(2, Serv.getResponsible().getId());
            ps.setDate(3,Serv.getRegistrationDate());
            ps.setString(4,Serv.getOrigin());
            ps.setInt(5,Serv.getTotalSamples());
            ps.setString(6,Serv.getShipmentRecord());
            ps.setBoolean(7,Serv.getBillingPending());
            ps.setInt(8,Serv.getBillNumber());
            ps.setString(9,Serv.getServiceOrder());
            ps.setDate(10,Serv.getBillingDate());
            
            ID = ps.executeUpdate();
        } finally {
            close(ps);
        }
        return ID;
        //TODO implement
    }
    
    Boolean remove(ServiceRequest Serv, Connection connect) throws SQLException {
        Boolean result = false;
        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_DELETE);
            ps.setInt(1, Serv.getId());
            ps.executeUpdate();
            result = true;
        } finally {
            close(ps);
        }
        return result;
        //TODO implement
    }
    
    Boolean update(ServiceRequest Serv, Connection connect) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = connect.prepareStatement(SQL_UPDATE, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, Serv.getId());
            ps.setInt(2, Serv.getClient().getId());
            ps.setInt(3, Serv.getResponsible().getId());
            ps.setDate(4,Serv.getRegistrationDate());
            ps.setString(5,Serv.getOrigin());
            ps.setInt(6,Serv.getTotalSamples());
            ps.setString(7,Serv.getShipmentRecord());
            ps.setBoolean(8,Serv.getBillingPending());
            ps.setInt(9,Serv.getBillNumber());
            ps.setString(10,Serv.getServiceOrder());
            ps.setDate(11,Serv.getBillingDate());            
            ps.setInt(12, Serv.getId());
            ID = ps.executeUpdate();
            
        } finally {
            close(ps);
        }
        return ID;
        //TODO implement
    }
    
    ServiceRequest find(Integer id, Connection connect) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT);
            ps.setString(1, id);
            rs = ps.executeQuery();
            ServiceRequest serq = new ServiceRequest();
            result = null;
            serq = get_ServiceRequest(rs);
            return serq;
        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
    
    List findAll(Connection connect) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT_All);
            rs = ps.executeQuery();
            List results = new ArrayList();
            ServiceRequest serq = new ServiceRequest();
            result = null;
            while (rs.next()) {
                serq = get_ServiceRequest(rs);
                results.add(serq);}
            return result;
        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
    
    List select_by_property(String camp, String Value, Connection connect)throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connect.prepareStatement(SQL_SELECT_PROPERTY);
            ps.setString(1, camp);
            ps.setString(2, value);
            rs = ps.executeQuery();
            List results = new ArrayList();
            ServiceRequest serq = new ServiceRequest();
            result = null;
            while (rs.next()) {
                serq = get_ServiceRequest(rs);
                results.add(serq);}
            return result;

        } finally {
            close(rs);
            close(ps);  
        }
        //TODO implement
    }
        
    ServiceRequest get_ServiceRequest (ResultSet rs) throws SQLException {
 
        ServiceRequest serq = new ServiceRequest();
        serq.setId(rs.getInt("id"));
        serq.setClient(rs.getInt("clientId"));
        serq.setEmployee(rs.getEmployee("employeeId"));
        serq.setRegistrationDate(rs.getDate("registrationDate"));
        serq.setOrigin(rs.getString("origin"));
        serq.setTotalSamples(rs.getInt("totalSamples"));
        serq.setShipmentRecord(rs.getString("shipmentrecord"));
        serq.setBillingPending(rs.getBoolean("billingPending"));
        serq.setNumber(rs.getInt("number"));
        serq.setServiceOrder(rs.getString("serviceOrder"));
        serq.setBillingDate(rs.getDate("billingDate"));

        return serq;
    }
}

